package by.grsu.crs.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import by.grsu.crs.dao.CarDao;
import by.grsu.crs.dao.OrderDao;
import by.grsu.crs.dao.StatusDao;
import by.grsu.crs.dao.UserDao;
import by.grsu.crs.model.ICar;
import by.grsu.crs.model.Order;
import by.grsu.crs.model.Status;
import by.grsu.crs.model.User;

public class OrderDaoImpl implements OrderDao {
	private static final String INSERT_NEW_ORDER 
		= "INSERT INTO orders(order_number, uid, cid, order_began, order_ended, sid, notice) VALUES (?, ?, ?, ?, ?, ?, ?)";
	
	private static final String SELECT_ALL_ORDERS
		= "SELECT id, order_number, uid, cid, order_began, order_ended, sid, notice FROM orders";
	
	private static final String SELECT_ORDER_BY_ID 
		= "SELECT id, order_number, uid, cid, order_began, order_ended, sid, notice FROM orders WHERE id = ?";
	
	private static final String SELECT_ORDER_BY_ORDER_NUMBER
		= "SELECT id, order_number, uid, cid, order_began, order_ended, sid, notice FROM orders WHERE order_number = ?";
	
	private static final String SELECT_ORDERS_BY_CLIENT
		= "SELECT id, order_number, uid, cid, order_began, order_ended, sid, notice FROM orders WHERE uid = ?";
	
	private static final String SELECT_ORDERS_BY_CAR
		= "SELECT id, order_number, uid, cid, order_began, order_ended, sid, notice FROM orders WHERE cid = ?";
	
	private static final String SELECT_ORDERS_BY_BEGAN_DATE
		= "SELECT id, order_number, uid, cid, order_began, order_ended, sid, notice FROM orders WHERE order_began = ?";
	
	private static final String SELECT_ORDERS_BY_ENDED_DATE
		= "SELECT id, order_number, uid, cid, order_began, order_ended, sid, notice FROM orders WHERE order_ended = ?";
	
	private static final String SELECT_ORDERS_BY_STATUS
		= "SELECT id, order_number, uid, cid, order_began, order_ended, sid, notice FROM orders WHERE sid = ?";
	
	private static final String DELETE_ORDER_BY_ID 
		= "DELETE FROM orders WHERE id = ?;";
	
	private static final String UPDATE_ORDER_BY_ID 
		= "UPDATE orders SET order_number = ?, uid = ?, cid = ?, order_began = ?, order_ended = ?, sid = ?, notice = ? WHERE id = ?";
	
	private Connection dbConnection;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	public OrderDaoImpl(Connection dbConnection) {
		this.dbConnection = dbConnection;
	}
	

	public boolean addOrder(Order order) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(INSERT_NEW_ORDER);
			preparedStatement.setInt(1, order.getOrderNumber());
			preparedStatement.setInt(2, order.getClient().getId());
			preparedStatement.setInt(3, order.getCar().getId());
			preparedStatement.setDate(4, (java.sql.Date) order.getOrderBegan());
			preparedStatement.setDate(5, (java.sql.Date) order.getOrderEnded());
			preparedStatement.setInt(6, order.getStatus().getId());
			preparedStatement.setString(7, order.getNotice());
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}

	public List<Order> getAllOrders() throws SQLException {
		List<Order> orders = new ArrayList<Order>();
		CarDao carDao = new CarDaoImpl(dbConnection);
		UserDao userDao = new UserDaoImpl(dbConnection);
		StatusDao statusDao = new StatusDaoImpl(dbConnection); 
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ALL_ORDERS);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				orders.add(new Order(resultSet.getInt("id"),
						resultSet.getInt("order_number"),
						userDao.getUserById(resultSet.getInt("uid")),
						carDao.getCarById(resultSet.getInt("cid")),
						resultSet.getDate("order_began"),
						resultSet.getDate("order_ended"),
						statusDao.getStatusById(resultSet.getInt("sid")),
						resultSet.getString("notice")));
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return orders;
	}

	public Order getOrderById(int id) throws SQLException {
		Order order = null;
		CarDao carDao = new CarDaoImpl(dbConnection);
		UserDao userDao = new UserDaoImpl(dbConnection);
		StatusDao statusDao = new StatusDaoImpl(dbConnection);
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ORDER_BY_ID);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			
			resultSet.last();			
			if(resultSet.getRow() != 0) {
				resultSet.first();
				
				int orderNumber = resultSet.getInt("order_number");
				User client = userDao.getUserById(resultSet.getInt("uid"));
				ICar car = carDao.getCarById(resultSet.getInt("cid"));
				Date orderBegan = resultSet.getDate("order_began");
				Date orderEnded = resultSet.getDate("order_ended");
				Status status = statusDao.getStatusById(resultSet.getInt("sid"));
				String notice = resultSet.getString("notice");
				
				order = new Order(id, orderNumber, client, car, orderBegan, orderEnded, status, notice);
			}		
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return order;
	}

	public Order getOrderByOrderNumber(int orderNumber) throws SQLException {
		Order order = null;
		CarDao carDao = new CarDaoImpl(dbConnection);
		UserDao userDao = new UserDaoImpl(dbConnection);
		StatusDao statusDao = new StatusDaoImpl(dbConnection);
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ORDER_BY_ORDER_NUMBER);
			preparedStatement.setInt(1, orderNumber);
			resultSet = preparedStatement.executeQuery();
			
			resultSet.last();			
			if(resultSet.getRow() != 0) {
				resultSet.first();
				
				int id = resultSet.getInt("id");
				User client = userDao.getUserById(resultSet.getInt("uid"));
				ICar car = carDao.getCarById(resultSet.getInt("cid"));
				Date orderBegan = resultSet.getDate("order_began");
				Date orderEnded = resultSet.getDate("order_ended");
				Status status = statusDao.getStatusById(resultSet.getInt("sid"));
				String notice = resultSet.getString("notice");
				
				order = new Order(id, orderNumber, client, car, orderBegan, orderEnded, status, notice);
			}		
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return order;
	}

	public List<Order> getOrdersByClient(User client) throws SQLException {
		List<Order> orders = new ArrayList<Order>();
		CarDao carDao = new CarDaoImpl(dbConnection);
		UserDao userDao = new UserDaoImpl(dbConnection);
		StatusDao statusDao = new StatusDaoImpl(dbConnection); 
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ORDERS_BY_CLIENT);
			preparedStatement.setInt(1, client.getId());
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				orders.add(new Order(resultSet.getInt("id"),
						resultSet.getInt("order_number"),
						userDao.getUserById(resultSet.getInt("uid")),
						carDao.getCarById(resultSet.getInt("cid")),
						resultSet.getDate("order_began"),
						resultSet.getDate("order_ended"),
						statusDao.getStatusById(resultSet.getInt("sid")),
						resultSet.getString("notice")));
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return orders;
	}

	public List<Order> getOrdersByCar(ICar car) throws SQLException {
		List<Order> orders = new ArrayList<Order>();
		CarDao carDao = new CarDaoImpl(dbConnection);
		UserDao userDao = new UserDaoImpl(dbConnection);
		StatusDao statusDao = new StatusDaoImpl(dbConnection); 
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ORDERS_BY_CAR);
			preparedStatement.setInt(1, car.getId());
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				orders.add(new Order(resultSet.getInt("id"),
						resultSet.getInt("order_number"),
						userDao.getUserById(resultSet.getInt("uid")),
						carDao.getCarById(resultSet.getInt("cid")),
						resultSet.getDate("order_began"),
						resultSet.getDate("order_ended"),
						statusDao.getStatusById(resultSet.getInt("sid")),
						resultSet.getString("notice")));
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return orders;
	}

	public List<Order> getOrdersByBeganDate(Date orderBegan) throws SQLException {
		List<Order> orders = new ArrayList<Order>();
		CarDao carDao = new CarDaoImpl(dbConnection);
		UserDao userDao = new UserDaoImpl(dbConnection);
		StatusDao statusDao = new StatusDaoImpl(dbConnection); 
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ORDERS_BY_BEGAN_DATE);
			preparedStatement.setDate(1, (java.sql.Date) orderBegan);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				orders.add(new Order(resultSet.getInt("id"),
						resultSet.getInt("order_number"),
						userDao.getUserById(resultSet.getInt("uid")),
						carDao.getCarById(resultSet.getInt("cid")),
						resultSet.getDate("order_began"),
						resultSet.getDate("order_ended"),
						statusDao.getStatusById(resultSet.getInt("sid")),
						resultSet.getString("notice")));
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return orders;
	}

	public List<Order> getOrdersByEndedDate(Date orderEnded) throws SQLException {
		List<Order> orders = new ArrayList<Order>();
		CarDao carDao = new CarDaoImpl(dbConnection);
		UserDao userDao = new UserDaoImpl(dbConnection);
		StatusDao statusDao = new StatusDaoImpl(dbConnection); 
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ORDERS_BY_ENDED_DATE);
			preparedStatement.setDate(1, (java.sql.Date) orderEnded);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				orders.add(new Order(resultSet.getInt("id"),
						resultSet.getInt("order_number"),
						userDao.getUserById(resultSet.getInt("uid")),
						carDao.getCarById(resultSet.getInt("cid")),
						resultSet.getDate("order_began"),
						resultSet.getDate("order_ended"),
						statusDao.getStatusById(resultSet.getInt("sid")),
						resultSet.getString("notice")));
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return orders;
	}

	public List<Order> getOrdersByStatus(Status status) throws SQLException {
		List<Order> orders = new ArrayList<Order>();
		CarDao carDao = new CarDaoImpl(dbConnection);
		UserDao userDao = new UserDaoImpl(dbConnection);
		StatusDao statusDao = new StatusDaoImpl(dbConnection); 
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ORDERS_BY_STATUS);
			preparedStatement.setInt(1, status.getId());
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				orders.add(new Order(resultSet.getInt("id"),
						resultSet.getInt("order_number"),
						userDao.getUserById(resultSet.getInt("uid")),
						carDao.getCarById(resultSet.getInt("cid")),
						resultSet.getDate("order_began"),
						resultSet.getDate("order_ended"),
						statusDao.getStatusById(resultSet.getInt("sid")),
						resultSet.getString("notice")));
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return orders;
	}

	public boolean deleteOrderByID(int id) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(DELETE_ORDER_BY_ID);
			preparedStatement.setInt(1, id);
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}

	public boolean updateOrderById(int id, Order order) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(UPDATE_ORDER_BY_ID);
			preparedStatement.setInt(1, order.getOrderNumber());
			preparedStatement.setInt(2, order.getClient().getId());
			preparedStatement.setInt(3, order.getCar().getId());
			preparedStatement.setDate(4, (java.sql.Date) order.getOrderBegan());
			preparedStatement.setDate(5, (java.sql.Date) order.getOrderEnded());
			preparedStatement.setInt(6, order.getStatus().getId());
			preparedStatement.setString(7, order.getNotice());
			preparedStatement.setInt(8, id);
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}

}
